package com.cxb.utils;

import java.io.File;
import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

import com.cxb.entity.DriverData;
import com.cxb.entity.Gps;
import com.cxb.entity.GpsDestinationInfo;
import com.cxb.entity.SMS;
import com.cxb.manager.RootContext;

/**
 * @ClassName: DBHelp
 * @Description: 封装常用的数据库操作
 * @author 杨瑜 45154139@qq.com
 * @date 2013-10-23 下午10:03:37
 * 
 */
public class DBHelp {

	private SQLiteOpenHelper dbHelper = null;
	private Context mContext;

	public DBHelp(Context context) {
		dbHelper = DataBaseHelper.getInstance(context);
		mContext = context;
	}

	/**
	 * 说明:取得下一条短信(如果没有下一条则返回null) Author:陈伟斌 2012-12-27
	 * 
	 * @param currentSMSId
	 * @return
	 */
	public SMS getNextSMS(int currentSMSId) {
		SQLiteDatabase db = this.dbHelper.getReadableDatabase();
		String sql = "SELECT * FROM " + Constants.SMS_TABLENAME + " where id<"
				+ currentSMSId + " ORDER BY id desc LIMIT 0,1";

		Cursor cursor = db.rawQuery(sql, null);
		SMS sms = null;
		if (cursor.moveToNext()) {
			sms = new SMS();
			sms.id = cursor.getInt(cursor.getColumnIndex("id"));
			sms.date = cursor.getString(cursor.getColumnIndex("date"));
			sms.isRead = cursor.getInt(cursor.getColumnIndex("isRead")) == 1;
			sms.phoneNumber = cursor.getString(cursor
					.getColumnIndex("phoneNumber"));
			sms.smsbody = cursor.getString(cursor.getColumnIndex("content"));
		}

		if (!cursor.isClosed()) {
			cursor.close();
		}

		return sms;
	}

	/**
	 * 说明:取得上一条短信(如果没有上一条则返回null) Author:陈伟斌 2012-12-27
	 * 
	 * @param currentSMSId
	 * @return
	 */
	public SMS getPreSMS(int currentSMSId) {
		SQLiteDatabase db = this.dbHelper.getReadableDatabase();
		String sql = "SELECT * FROM " + Constants.SMS_TABLENAME + " where id>"
				+ currentSMSId + " ORDER BY id asc LIMIT 0,1";

		Cursor cursor = db.rawQuery(sql, null);
		SMS sms = null;
		if (cursor.moveToNext()) {
			sms = new SMS();
			sms.id = cursor.getInt(cursor.getColumnIndex("id"));
			sms.date = cursor.getString(cursor.getColumnIndex("date"));
			sms.isRead = cursor.getInt(cursor.getColumnIndex("isRead")) == 1;
			sms.phoneNumber = cursor.getString(cursor
					.getColumnIndex("phoneNumber"));
			sms.smsbody = cursor.getString(cursor.getColumnIndex("content"));
		}

		if (!cursor.isClosed()) {
			cursor.close();
		}

		return sms;
	}

	/**************** 短信数据库操作开始 *****************/
	/**
	 * 说明:增加短信息 Author:陈伟斌 2012-12-25
	 * 
	 * @param
	 */
	public void addSMSList(List<SMS> list) {

		for (SMS sms : list) {

			ContentValues values = new ContentValues();
			values.put("content", sms.smsbody);
			values.put("phoneNumber", sms.phoneNumber);
			values.put("isRead", sms.isRead ? 1 : 0);
			values.put("date", sms.date);
			SQLiteDatabase db = dbHelper.getWritableDatabase();
			db.insert(Constants.SMS_TABLENAME, "_id", values);
		}
	}

	/**
	 * 说明:增加短信息 Author:陈伟斌 2012-12-25
	 * 
	 * @param
	 */
	public void addSMS(SMS sms) {

		ContentValues values = new ContentValues();
		values.put("content", sms.smsbody);
		values.put("phoneNumber", sms.phoneNumber);
		values.put("isRead", sms.isRead ? 1 : 0);
		values.put("date", sms.date);
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.insert(Constants.SMS_TABLENAME, "_id", values);

	}

	/**
	 * 说明:根据id删除短信 Author:陈伟斌 2012-12-25
	 * 
	 * @param id
	 */
	public void deleteSMSById(int id) {
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.beginTransaction();
		db.delete(Constants.SMS_TABLENAME, "id=?", new String[] { id + "" });
		db.endTransaction();
	}

	public void deleteDriverData(int id) {
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.delete("driverdata", "id=?", new String[] { id + "" });
	}

	public void addDriverData(DriverData driverData) {

		ContentValues values = new ContentValues();
		values.put("time", driverData.getTime());
		values.put("magneticX", driverData.getMagneticX());
		values.put("magneticY", driverData.getMagneticY());
		values.put("magneticZ", driverData.getMagneticZ());
		values.put("gsensorX", driverData.getGsensorX());
		values.put("gsensorY", driverData.getGsensorY());
		values.put("gsensorZ", driverData.getGsensorZ());
		values.put("lat", driverData.getLat());
		values.put("lng", driverData.getLng());
		values.put("speed", driverData.getSpeed());
		values.put("height", driverData.getHeight());

		SQLiteDatabase db = dbHelper.getWritableDatabase();
		// db.setTransactionSuccessful();
		// db.beginTransaction();
		db.insert("driverdata", "_id", values);
		// db.endTransaction();

		// List<DriverData> data = getDriverDataList(10);

	}

	// private class DriverDataBucket
	// {
	// private DriverData driverDatas;
	// }
	//
	// private DriverDataBucket getDriverDatas()
	// {
	// return null;
	// }
	//
	// private
	//
	// /**批量新增**/
	// private void driversInsert2DB(SQLiteDatabase db)
	// {
	// if(db == null)
	// return;
	// }
	//
	//

	public List<DriverData> getDriverDataList(int max) {
		SQLiteDatabase db = this.dbHelper.getReadableDatabase();
		// db.beginTransaction();
		String sql = "select * from  driverdata order by id desc limit " + max;
		Cursor cursor = db.rawQuery(sql, null);
		DriverData driverData = null;
		List<DriverData> list = new ArrayList<DriverData>();
		while (cursor.moveToNext()) {
			driverData = new DriverData();
			driverData.setId(cursor.getInt(cursor.getColumnIndex("id")));
			driverData.setLat(cursor.getDouble(cursor.getColumnIndex("lat")));
			driverData.setLng(cursor.getDouble(cursor.getColumnIndex("lng")));
			driverData.setGsensorX(cursor.getFloat(cursor
					.getColumnIndex("gsensorX")));
			driverData.setGsensorY(cursor.getFloat(cursor
					.getColumnIndex("gsensorY")));
			driverData.setGsensorZ(cursor.getFloat(cursor
					.getColumnIndex("gsensorZ")));
			driverData.setMagneticX(cursor.getFloat(cursor
					.getColumnIndex("magneticX")));
			driverData.setMagneticY(cursor.getFloat(cursor
					.getColumnIndex("magneticY")));
			driverData.setMagneticZ(cursor.getFloat(cursor
					.getColumnIndex("magneticX")));
			driverData
					.setSpeed(cursor.getFloat(cursor.getColumnIndex("speed")));
			driverData.setTime(cursor.getLong(cursor.getColumnIndex("time")));
			driverData.setHeight(cursor.getFloat(cursor
					.getColumnIndex("height")));
			list.add(driverData);
		}

		if (!cursor.isClosed()) {
			cursor.close();
		}
		// db.endTransaction();
		return list;
	}

	public void deleteGps(int id) {
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.delete("gps", "id=?", new String[] { id + "" });
	}

	private int addGpsCount = 0;
	public final int MAX_GPS_CACHE_SIZE = 1200 * 4 * 7;

	public void addGps(Gps gps) {

		ContentValues values = new ContentValues();
		values.put("time", gps.time);
		values.put("angle", gps.angle);
		values.put("height", gps.height);
		values.put("lat", gps.lat);
		values.put("lng", gps.lng);
		values.put("mode", gps.mode);
		values.put("pdop", gps.pdop);
		values.put("hdop", gps.hdop);
		values.put("vdop", gps.vdop);
		values.put("speed", gps.speed);
		values.put("magneticN", gps.magneticN);
		values.put("satelite", gps.sateliteCount);
		// magneticN TEXT,satelite TEXT
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.insert("gps", "_id", values);
		// 缓存太多时，清除部分旧数据

		addGpsCount++;

		if (addGpsCount > 10) {
			// 清空数据
			try {

				int result = db.delete("gps",
						"id not in (select id from gps order by id desc LIMIT "
								+ MAX_GPS_CACHE_SIZE + ")", null);
				System.out.println(result);
			} catch (Exception e) {
				e.printStackTrace();
			}

			addGpsCount = 0;
		}

	}

	public Gps getGps() {
		SQLiteDatabase db = this.dbHelper.getReadableDatabase();

		String sql = "select * from  gps order by id desc";
		Cursor cursor = db.rawQuery(sql, null);
		Gps gps = null;
		if (cursor.moveToNext()) {
			gps = new Gps();
			gps.id = cursor.getInt(cursor.getColumnIndex("id"));
			gps.angle = cursor.getFloat(cursor.getColumnIndex("angle"));
			gps.pdop = cursor.getFloat(cursor.getColumnIndex("pdop"));
			gps.hdop = cursor.getFloat(cursor.getColumnIndex("hdop"));
			gps.magneticN = cursor.getFloat(cursor.getColumnIndex("magneticN"));
			gps.vdop = cursor.getFloat(cursor.getColumnIndex("vdop"));
			gps.lat = cursor.getDouble(cursor.getColumnIndex("lat"));
			gps.lng = cursor.getDouble(cursor.getColumnIndex("lng"));
			gps.height = cursor.getDouble(cursor.getColumnIndex("height"));
			gps.speed = cursor.getFloat(cursor.getColumnIndex("speed"));
			gps.mode = cursor.getInt(cursor.getColumnIndex("mode"));
			gps.sateliteCount = cursor
					.getInt(cursor.getColumnIndex("satelite"));
			gps.time = cursor.getString(cursor.getColumnIndex("time"));
		}

		if (!cursor.isClosed()) {
			cursor.close();
		}
		return gps;
	}

	public List<Gps> getGpsList(int max) {
		SQLiteDatabase db = this.dbHelper.getReadableDatabase();

		String sql = "select * from  gps order by id desc limit " + max;
		Cursor cursor = db.rawQuery(sql, null);
		Gps gps = null;
		List<Gps> list = new ArrayList<Gps>();
		while (cursor.moveToNext()) {
			gps = new Gps();
			gps.id = cursor.getInt(cursor.getColumnIndex("id"));
			gps.angle = cursor.getFloat(cursor.getColumnIndex("angle"));
			gps.pdop = cursor.getFloat(cursor.getColumnIndex("pdop"));
			gps.hdop = cursor.getFloat(cursor.getColumnIndex("hdop"));
			gps.magneticN = cursor.getFloat(cursor.getColumnIndex("magneticN"));
			gps.vdop = cursor.getFloat(cursor.getColumnIndex("vdop"));
			gps.lat = cursor.getDouble(cursor.getColumnIndex("lat"));
			gps.lng = cursor.getDouble(cursor.getColumnIndex("lng"));
			gps.height = cursor.getDouble(cursor.getColumnIndex("height"));
			gps.speed = cursor.getInt(cursor.getColumnIndex("speed"));
			gps.mode = cursor.getInt(cursor.getColumnIndex("mode"));
			gps.sateliteCount = cursor
					.getInt(cursor.getColumnIndex("satelite"));
			gps.time = cursor.getString(cursor.getColumnIndex("time"));
			list.add(gps);
		}

		if (!cursor.isClosed()) {
			cursor.close();
		}
		return list;
	}

	/**
	 * 说明:将短信设置为已读 Author:陈伟斌 2013-1-9
	 */
	public void setSMSHasRead(int id) {

		SQLiteDatabase db = dbHelper.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("isRead", 1);
		db.update(Constants.SMS_TABLENAME, values, "id=?", new String[] { id
				+ "" });
	}

	/**
	 * 说明:删除所有短信 Author:陈伟斌 2012-12-25
	 */
	public void deleteAllSMS() {
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.execSQL("delete from " + Constants.SMS_TABLENAME);
	}

	/**
	 * 说明:取得总共有多少条短信 Author:陈伟斌 2012-12-26
	 * 
	 * @return
	 */
	public int getSMSTotalCount() {
		SQLiteDatabase db = this.dbHelper.getReadableDatabase();
		String sql = "select * from " + Constants.SMS_TABLENAME;
		Cursor cursor = db.rawQuery(sql, null);
		return cursor.getCount();
	}

	/**
	 * 说明:取得总共有多少条未读短信 Author:陈伟斌 2012-12-26
	 * 
	 * @return
	 */
	public int getSMSUnReadCount() {

		SQLiteDatabase db = this.dbHelper.getReadableDatabase();
		String sql = "select * from " + Constants.SMS_TABLENAME
				+ " where isRead=0";
		Cursor cursor = db.rawQuery(sql, null);
		int count = cursor.getCount();
		if (!cursor.isClosed()) {
			cursor.close();
		}
		return count;
	}

	/**
	 * 说明:取得所有短信(按时间排序) Author:陈伟斌 2012-12-25
	 * 
	 * @return
	 */
	public List<SMS> getAllSMS() {

		SQLiteDatabase db = this.dbHelper.getReadableDatabase();

		String sql = "select * from " + Constants.SMS_TABLENAME
				+ " order by id desc";
		Cursor cursor = db.rawQuery(sql, null);

		SMS sms = null;
		List<SMS> list = new ArrayList<SMS>();
		while (cursor.moveToNext()) {
			sms = new SMS();
			sms.id = cursor.getInt(cursor.getColumnIndex("id"));
			sms.date = cursor.getString(cursor.getColumnIndex("date"));
			sms.isRead = cursor.getInt(cursor.getColumnIndex("isRead")) == 1;
			sms.phoneNumber = cursor.getString(cursor
					.getColumnIndex("phoneNumber"));
			sms.smsbody = cursor.getString(cursor.getColumnIndex("content"));

			list.add(sms);
		}
		if (!cursor.isClosed()) {
			cursor.close();
		}
		return list;
	}

	public void addHistoryList(List<GpsDestinationInfo> list) {
		for (GpsDestinationInfo destination : list) {
			addHistory(destination);
		}
	}

	public void addHistory(GpsDestinationInfo destination) {

		if(destination.destName==null||"".equals(destination.destName)){
			return;
		}
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		// 删除同名记录,最大只保留100条记录
		db.execSQL("DELETE FROM " + Constants.HISTORY_TABLENAME
				+ " WHERE name='" + destination.destName + "'");

		ContentValues values = new ContentValues();
		values.put("name", destination.destName);
		values.put("lng", (long) (destination.longitude * 1000000));
		values.put("lat", (long) (destination.latitude * 1000000));
		db.insert(Constants.HISTORY_TABLENAME, "_id", values);

		String sql = "select * from " + Constants.HISTORY_TABLENAME;
		Cursor cursor = db.rawQuery(sql, null);
		int count = cursor.getCount();
		if (count > 100) {
			db.execSQL("DELETE FROM " + Constants.HISTORY_TABLENAME
					+ " WHERE id in (SELECT min(id) from "
					+ Constants.HISTORY_TABLENAME + ")");
		}
	}

	public List<GpsDestinationInfo> getAllHistory() {

		// /////////////////////////高德历史 /////////////////////////////////
		List<GpsDestinationInfo> list = new ArrayList<GpsDestinationInfo>();
		try {

			if (Config.MAP_AUTONAVI == Config.getMapDefault(mContext)&&Config.NAVI_HISTORY_FROM_MAP) {
				RootContext rootContext = RootContext.getInstance();

				rootContext
						.runCommand("chown root:root /data/data/com.autonavi.xmgd.navigator");
				rootContext
						.runCommand("chown root:root /data/data/com.autonavi.xmgd.navigator/databases/");
				Thread.sleep(1000);
				rootContext
						.runCommand("chown root:root /data/data/com.autonavi.xmgd.navigator/databases/autonavi.db");
				Thread.sleep(1000);
				rootContext
						.runCommand("chmod 777 /data/data/com.autonavi.xmgd.navigator/databases/");
				Thread.sleep(1000);
				String path = "/data/data/com.autonavi.xmgd.navigator/databases/autonavi.db";
				if (!new File(path).exists()) {
					return list;
				}

				SQLiteDatabase db = mContext.openOrCreateDatabase(path,
						Context.MODE_WORLD_WRITEABLE, null);

				Cursor cursor = db.query("HistoryPoiTable", null, null, null,
						null, null, "mOperateTime desc ");
				GpsDestinationInfo destination = null;
				while (cursor.moveToNext()) {
					destination = new GpsDestinationInfo();
					// destination.id =
					// cursor.getInt(cursor.getColumnIndex("id"));
					destination.destName = cursor.getString(cursor
							.getColumnIndex("szName"));
					destination.longitude = cursor.getLong(cursor
							.getColumnIndex("x")) / 1000000.0f;
					destination.latitude = cursor.getLong(cursor
							.getColumnIndex("y")) / 1000000.0f;
					destination.receiveTime = cursor.getString(cursor
							.getColumnIndex("mOperateTime"));
					list.add(destination);

				}

				cursor.close();
				db.close();
			} else{

				SQLiteDatabase db = this.dbHelper.getReadableDatabase();

				String sql = "select * from " + Constants.HISTORY_TABLENAME
						+ " order by id desc";
				Cursor cursor = db.rawQuery(sql, null);
				GpsDestinationInfo destination = null;
				while (cursor.moveToNext()) {
					destination = new GpsDestinationInfo();
					// destination.id =
					// cursor.getInt(cursor.getColumnIndex("id"));
					destination.destName = cursor.getString(cursor
							.getColumnIndex("name"));
					destination.longitude = cursor.getLong(cursor
							.getColumnIndex("lng")) / 1000000.0f;
					destination.latitude = cursor.getLong(cursor
							.getColumnIndex("lat")) / 1000000.0f;
					destination.id = cursor.getInt(cursor.getColumnIndex("id"));
					list.add(destination);

				}
				return list;
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;

	}

	public void deleteAllHistory() {

		try {

			if (Config.MAP_AUTONAVI == Config.getMapDefault(mContext)&&Config.NAVI_HISTORY_FROM_MAP) {
				RootContext rootContext = RootContext.getInstance();
				String path = "/data/data/com.autonavi.xmgd.navigator/databases/autonavi.db";
				if (!new File(path).exists()) {
					return;
				}

				rootContext
						.runCommand("chmod 777 /data/data/com.autonavi.xmgd.navigator/databases/autonavi*");

				SQLiteDatabase db = mContext.openOrCreateDatabase(path,
						Context.MODE_WORLD_WRITEABLE, null);
				db.execSQL("delete from HistoryPoiTable");
				db.close();
			} else {
				SQLiteDatabase db = dbHelper.getWritableDatabase();
				db.execSQL("delete from " + Constants.HISTORY_TABLENAME);
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public int getHistoryTotalCount() {
		SQLiteDatabase db = this.dbHelper.getReadableDatabase();
		String sql = "select * from " + Constants.HISTORY_TABLENAME;
		Cursor cursor = db.rawQuery(sql, null);
		return cursor.getCount();
	}

	public GpsDestinationInfo getNextHistory(int currentHistoryId) {
		SQLiteDatabase db = this.dbHelper.getReadableDatabase();
		String sql = "SELECT * FROM " + Constants.HISTORY_TABLENAME
				+ " where id<" + currentHistoryId
				+ " ORDER BY id desc LIMIT 0,1";

		Cursor cursor = db.rawQuery(sql, null);
		GpsDestinationInfo destination = null;
		if (cursor.moveToNext()) {
			destination = new GpsDestinationInfo();
			destination.id = cursor.getInt(cursor.getColumnIndex("id"));
			destination.destName = cursor.getString(cursor
					.getColumnIndex("name"));
			destination.longitude = cursor
					.getLong(cursor.getColumnIndex("lng")) / 1000000.0f;
			destination.latitude = cursor.getLong(cursor.getColumnIndex("lat")) / 1000000.0f;
		}

		if (!cursor.isClosed()) {
			cursor.close();
		}
		return destination;
	}

	public GpsDestinationInfo getPreHistory(int currentHistoryId) {
		SQLiteDatabase db = this.dbHelper.getReadableDatabase();
		String sql = "SELECT * FROM " + Constants.HISTORY_TABLENAME
				+ " where id>" + currentHistoryId
				+ " ORDER BY id asc LIMIT 0,1";

		Cursor cursor = db.rawQuery(sql, null);
		GpsDestinationInfo destination = null;
		if (cursor.moveToNext()) {
			destination = new GpsDestinationInfo();
			destination.id = cursor.getInt(cursor.getColumnIndex("id"));
			destination.destName = cursor.getString(cursor
					.getColumnIndex("name"));
			destination.longitude = cursor
					.getLong(cursor.getColumnIndex("lng")) / 1000000.0f;
			destination.latitude = cursor.getLong(cursor.getColumnIndex("lat")) / 1000000.0f;
		}

		if (!cursor.isClosed()) {
			cursor.close();
		}
		return destination;
	}

	public void deleteHistoryById(int id) {
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.delete(Constants.HISTORY_TABLENAME, "id=?", new String[] { id + "" });
	}

	public void deleteHistoryByName(String address) {
		if (Config.MAP_AUTONAVI == Config.getMapDefault(mContext)&&Config.NAVI_HISTORY_FROM_MAP) {
			RootContext rootContext = RootContext.getInstance();
			rootContext
					.runCommand("chmod 777 /data/data/com.autonavi.xmgd.navigator/databases/autonavi*");

			String path = "/data/data/com.autonavi.xmgd.navigator/databases/autonavi.db";
			if (!new File(path).exists()) {
				path = "/mnt/sdcard/autonavi.db";
			}
			SQLiteDatabase db = mContext.openOrCreateDatabase(path,
					Context.MODE_WORLD_WRITEABLE, null);
			db.delete("HistoryPoiTable", "mOperateTime=?",
					new String[] { address });
			db.close();
		}

	}

	/***************** 轨迹点 ***********/
	public void addPOI(String uploadUrl) {
		ContentValues values = new ContentValues();
		values.put("uploadUrl", uploadUrl);
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.insert(Constants.UPLOAD_POI_TABLENAME, "_id", values);
	}

	/**
	 * 说明:取得数据库中的一条轨迹点上传信息 Author:陈伟斌 2013-1-24
	 * 
	 * @return
	 */
	public String getPOIRecord() {

		SQLiteDatabase db = this.dbHelper.getReadableDatabase();
		String uploadUrl = null;
		String sql = "select * from " + Constants.UPLOAD_POI_TABLENAME
				+ " order by id asc";
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor.moveToNext()) {
			uploadUrl = cursor.getString(cursor.getColumnIndex("uploadUrl"));
		}
		if (!cursor.isClosed()) {
			cursor.close();
		}
		return uploadUrl;
	}

	public List<String> getPOIRecordList() {

		SQLiteDatabase db = this.dbHelper.getReadableDatabase();
		String uploadUrl = null;
		List<String> list = new ArrayList<String>();
		String sql = "select * from " + Constants.UPLOAD_POI_TABLENAME
				+ " order by id asc limit 15";
		Cursor cursor = db.rawQuery(sql, null);
		while (cursor.moveToNext()) {
			uploadUrl = cursor.getString(cursor.getColumnIndex("uploadUrl"));
			list.add(uploadUrl);
		}
		if (!cursor.isClosed()) {
			cursor.close();
		}
		return list;
	}

	public void deletePOIRecord(String uploadUrl) {

		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.execSQL("delete from " + Constants.UPLOAD_POI_TABLENAME
				+ "  where uploadUrl like '%" + uploadUrl + "%'");
	}

}

class DataBaseHelper extends SQLiteOpenHelper {

	private static DataBaseHelper dbBaseHelper = null;

	public DataBaseHelper(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
	}

	public static synchronized DataBaseHelper getInstance(Context context) {
		if (dbBaseHelper == null) {
			dbBaseHelper = new DataBaseHelper(context);
		}
		return dbBaseHelper;
	}

	public DataBaseHelper(Context context) {
		this(context, Constants.DB_NAME, null, 1);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		/****** 短信表 *******/
		db.execSQL("CREATE TABLE IF NOT EXISTS " + Constants.SMS_TABLENAME
				+ "(" + "id INTEGER PRIMARY KEY," + "content TEXT,"
				+ "phoneNumber TEXT," + "isRead INTEGER," + "date TEXT" + ");");

		/****** 导航历史记录表 *********/
		db.execSQL("CREATE TABLE IF NOT EXISTS " + Constants.HISTORY_TABLENAME
				+ "(" + "id INTEGER PRIMARY KEY," + "name TEXT," + "lng LONG,"
				+ "lat LONG" + ");");

		/****** 上傳記錄 *******/
		db.execSQL("CREATE TABLE IF NOT EXISTS "
				+ Constants.UPLOAD_POI_TABLENAME + "("
				+ "id INTEGER PRIMARY KEY,uploadUrl TEXT);");

		/****** GPS轨迹点 *******/
		db.execSQL("CREATE TABLE IF NOT EXISTS  gps("
				+ "id INTEGER PRIMARY KEY,angle TEXT,time TEXT,height TEXT,lat TEXT,lng TEXT,mode TEXT,pdop TEXT,hdop TEXT,vdop TEXT,speed TEXT, magneticN TEXT,satelite TEXT);");

		/********** driverdata *************/
		db.execSQL("CREATE TABLE IF NOT EXISTS  driverdata("
				+ "id INTEGER PRIMARY KEY,magneticX TEXT,magneticY TEXT,magneticZ TEXT,gsensorX TEXT,gsensorY TEXT,gsensorZ TEXT,lat TEXT,lng TEXT,speed TEXT,height TEXT,time TEXT);");

		/************ mqtt缓存协议 *************/
		db.execSQL("CREATE TABLE IF NOT EXISTS  mqtt("
				+ "id INTEGER PRIMARY KEY,protocol TEXT,time TEXT,msgId TEXT,qos TEXT);");

		// /****** wifi *******/
		// db.execSQL("CREATE WIFI IF NOT EXISTS " + Constants.WIFI + "("
		// + "id INTEGER PRIMARY KEY,ssid TEXT,password TEXT);");

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	}

	@Override
	public synchronized void close() {
		super.close();
	}

	@Override
	public void onOpen(SQLiteDatabase db) {
		super.onOpen(db);
	}

}
